'''
Created on Apr 1, 2012

@author: e911miri
'''

import random

import pymysql
import cProfile

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='[]', db='orchard')
cur= conn.cursor()
subjects= ["mathematics", "biology", "chemistry", "physics", "geography", "economics"]

def get_random_word(wordLen):
    word = ''
    for i in range(wordLen):
        word += random.choice('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')
    return word

def generate_random_result(reg_num):
    course1="english language"
    score1=random.randint(0, 100)
    course2= random.choice(subjects)
    score2=random.randint(0, 100)
    course3= random.choice(subjects)
    score3=random.randint(0, 100)
    course4= random.choice(subjects)
    score4=random.randint(0, 100)
    total= score1+score2+score3+score4
    query= "INSERT INTO results (reg_num, course1, score1, course2, score2, course3, score3, course4, score4, score) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')".format(reg_num,course1, score1, course2, score2, course3, score3, course4, score4, total)
    return query

def main():
    print("Now Generating Registration Numbers")
    for i in range(10000):
        wordLen = 10 #random.randint(5, 15)
        pin= random.randint(1000, 9999)
        word = get_random_word(wordLen)
        if i % 5000 == 0:
            conn.commit()
            print("I have generated {0} numbers".format(i))
        cur.execute("INSERT INTO candidates (reg_num, pin) VALUES (%s, %s)", (word, str(pin)))
    print("Registration Numbers generation completed!!")
    
    cur.close()
    print("Now Generating Results")
    c= conn.cursor()
    c.execute("select reg_num from candidates")
    i=0
    for x in c.fetchall():
        i+=1
        if i % 5000 == 0:
            conn.commit()
            print("I have saved {0} numbers".format(i))
        c.execute(generate_random_result(x[0]))
    c.close()
    conn.close()
main()
